package org.duang.db.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.duang.kit.SqlKit;
import org.duang.kit.ToolsKit;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class DBSession {

	private static Logger logger = LoggerFactory.getLogger(DBSession.class);
	// 每一个线程都有自己的连接
	public static final ThreadLocal<Connection> connContainer = new ThreadLocal<Connection>();
	public static final Object[] NULL_OBJECT = new Object[0];
		
	private static <T> T call(DBAction<T> dbAction){
		T result = null;
		Connection connection = null; 
		try{
			connection  = getConnection();
			if(null == connection){ throw new NullPointerException();}
			result = dbAction.execute(connection);
		} catch(Exception e){
			logger.warn(e.getMessage(), e);
		} finally {
			if(ToolsKit.isNotEmpty(connection)){
				close(connection);
			}
		}
		return result;
	}
	
	
	public static  List<String> getMysqlTables(){
		return call(new DBAction<List<String>>(){
			@Override
			public List<String> execute(Connection connection) throws SQLException {
				DBRunner run = new DBRunner(connection); 
				String sql = "select table_name from information_schema.tables where table_schema=?";	
				Object[] params = {connection.getCatalog()};
				List<Map<String,Object>> queryList =  run.query(sql, params);
				List<String> resultList = SqlKit.toList(queryList);
				return resultList;
			}
		});
	}
	
	/**
	 * 根据表名取出所有索引
	 * @param tableName		表名
	 * @return		索引数组集合
	 */
	public static List<String> getIndexs(final String tableName) {
		return call(new DBAction<List<String> >(){
			@Override
			public List<String> execute(Connection connection) throws SQLException {
				DBRunner run = new DBRunner(connection);  
				String sql = "show index from "+ tableName;
				String[] filterNames = {"Key_name"};
				List<Map<String,Object>> queryList = run.query(sql, filterNames, NULL_OBJECT);
				List<String> resultList = SqlKit.toList(queryList);
				return resultList;
			}
		});
	}
	
	/**
	 * 执行查询SQL语句
	 * @param querySql		查询SQL语句
	 * @param params			参数数组
	 * @return
	 */
	public static List<Map<String,Object>> query(final String querySql, final Object... params){
		return call(new DBAction<List<Map<String,Object>>>(){
			@Override
			public List<Map<String,Object>> execute(Connection connection) throws SQLException {
				DBRunner run = new DBRunner(connection);  
				return  run.query(querySql, params);
			}
		});
	}
	
	/**
	 *  执行SQL语句，用于update, delete等
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int execute(final String sql, final Object... params)  {
		return call(new DBAction<Integer>(){
			@Override
			public Integer execute(Connection connection) throws SQLException {
				DBRunner run = new DBRunner(connection);  
				return run.execute(sql, params);
			}
		});
	}
	

	private static Connection getConnection(){
		Connection connection = null;
		try {
			connection = connContainer.get();
			if(null == connection){
				connection =  Sql.getDataSource().getConnection();
				if(null != connection) connContainer.set(connection);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * 开启事务
	 */
	public static void startTransaction() {
		Connection connection = getConnection(); 
		try {
			if(null != connection){
				connection.setAutoCommit(false);
			}
		} catch (SQLException e) {
			logger.warn("StartTransaction is Error: "+e.getMessage(), e);
		}
	}
	/**
	 * 提交事务
	 */
	public static void commintTransaction() {
		Connection connection = getConnection(); 
		try {
			if(null != connection){
				connection.commit();
			}
		} catch (SQLException e) {
			logger.warn("CommintTransaction is Error: " + e.getMessage(), e);
		} finally{
			 try {
				if(null!= connection){
					connection.close();
					connContainer.remove();
				}
			} catch (SQLException e) {
				logger.warn(e.getMessage(), e);
			}
		}
	}
	
	/**
	 * 回滚事务
	 */
	public static void rollbakcTransaction() {
		Connection connection = getConnection(); 
		try {
			if(null != connection){
				connection.rollback();
			}
		} catch (SQLException e) {
			logger.warn("RollbakcTransaction is Error: " + e.getMessage(), e);
		} finally{
			 try {
				if(null!= connection) {
					connection.close();
					connContainer.remove();
				}
			} catch (SQLException e) {
				logger.warn(e.getMessage(), e);
			}
		}
	}
	
	
	public static void close(PreparedStatement stmt) {
		if(ToolsKit.isNotEmpty(stmt)){
			try {
				stmt.close();
			} catch (SQLException e) {
				logger.warn(e.getMessage(), e);
			}
		}
	}
	
	public static void close(Connection conn) {
		if(ToolsKit.isNotEmpty(conn)){
			try {
				conn.close();
				connContainer.remove();
			} catch (SQLException e) {
				logger.warn(e.getMessage(), e);
			}
		}
	}
	
}
